﻿using Dapper;
using System.Data.SqlClient;
using yunbo.Model.RMM;
using static System.Net.Mime.MediaTypeNames;

namespace yunbo.Repositorys.RMMRepositorys
{
    public class Rm_yclcgRepository
    {
        public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User ID=sa;Password=geili@2025;";

        public FYRm_yclcg GetFYRm_yclcg(int PageNumber, int PageSize)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = @$"select
                                    cg.[yclcg_code],
	                                cg.[create_time],
	                                cg.[yclcg_code],
	                                ks.[ks_name] as [yclcg_gys] ,
	                                cg.[yclcg_sl],
	                                ck.[ck_name] as [yclcg_ruck],
                                    cg.[create_by],
	                                cg.[yclcg_djzt],
	                                us.[user_name]
	                                from [dbo].[rm_yclcg] cg
	                                LEFT JOIN
                                   [dbo].[wbd_ck] as ck 
                                   ON cg.yclcg_ruck = ck.ck_code
                                   LEFT JOIN
                                 [dbo].[bd_ks] as ks
                                 ON cg.yclcg_gys=ks.ks_code
                                 LEFT JOIN
                                [dbo].[user] as us
                                ON cg.create_by=us.user_id
                               
                               ORDER BY  [yclcg_id] 
                               OFFSET ({PageNumber}-1)*{PageSize} ROWS
                               FETCH NEXT {PageSize} ROWS ONLY;";
                List<Rm_yclcg> rm_Yclcg = connection.Query<Rm_yclcg>(sql).ToList();
                string sql1 = "SELECT COUNT(*) FROM [dbo].[Rm_yclcg]";
                int zon = connection.Query<int>(sql1).FirstOrDefault();
                FYRm_yclcg fYRm_Yclcg = new FYRm_yclcg()
                {
                    rm_Yclcgs = rm_Yclcg,
                    zon = zon,
                };
                return fYRm_Yclcg;
            }
        }
        public int Addyclcg(Rm_yclcg rm_Yclcg)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = @$"insert into [dbo].[rm_yclcg](yclcg_code, yclcg_ruck, yclcg_gys, yclcg_ry, yclcg_time, wl_code, yclcg_zkje, yclcg_fjje, yclcg_jsfs, djbz, yclcg_sl, create_by, create_time, yclcg_zje, yclcg_ssje, yclcg_djzt, yclcg_zkl)
                                values('{rm_Yclcg.yclcg_code}','{rm_Yclcg.yclcg_ruck}','{rm_Yclcg.yclcg_gys}','{rm_Yclcg.yclcg_ry}','{rm_Yclcg.yclcg_time}','{rm_Yclcg.wl_code}','{rm_Yclcg.yclcg_zkje}','{rm_Yclcg.yclcg_fjje}','{rm_Yclcg.yclcg_jsfs}','{rm_Yclcg.djbz}','{rm_Yclcg.yclcg_sl}','{rm_Yclcg.create_by}','{rm_Yclcg.create_time}','{rm_Yclcg.yclcg_zje}','{rm_Yclcg.yclcg_ssje}','{rm_Yclcg.yclcg_djzt}','{rm_Yclcg.yclcg_zkl}')";
                int rm = connection.Execute(sql);
                return rm;
            }
        }
        public Rm_yclcg Get_Yclcg(string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = @$"SELECT 
  rm.*,
  ck.ck_name AS warehouse_name,
  ks.ks_name AS supplier_name
FROM 
  [dbo].[rm_yclcg] rm
LEFT JOIN 
  [dbo].[wbd_ck] ck ON rm.yclcg_ruck = ck.ck_code
LEFT JOIN 
  [dbo].[bd_ks] ks ON rm.yclcg_gys = ks.ks_code
WHERE 
  rm.yclcg_code = '{code}';";
                Rm_yclcg rm_Yclcg=connection.Query<Rm_yclcg>(sql).FirstOrDefault();
                return rm_Yclcg;
            }
           
        }
        public int UpdateYclcg(Rm_yclcg rm_Yclcg)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = @$"update [dbo].[rm_yclcg] set dhzt='{rm_Yclcg.dhzt}',dhcode='{rm_Yclcg.dhcode}',  yclcg_djzt='{rm_Yclcg.yclcg_djzt}'  where yclcg_code='{rm_Yclcg.yclcg_code}'";
                int rm = connection.Execute(sql);
                return rm;
            }
        }

        public FYRm_yclcg Getdh(int PageNumber, int PageSize)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $@"  select 
		                    cg.[dhzt],
		                    cg.[dhcode],
		                    cg.[yclcg_code],
		                    cg.[yclcg_time],
		                    cg.[yclcg_ry],
		                    cg.[create_by],
		                    cg.[create_time],
		                    cg.[djbz],
		                    us.[user_name],
		                    cg.yclcg_sl,
		                    cgs.yclcgxq_ydhsl
                            from [dbo].[rm_yclcg] cg
                            LEFT JOIN
                            [dbo].[user] as us
                             ON cg.create_by=us.user_id
                             LEFT JOIN
                            [dbo].[rm_yclcgxq] as cgs
                            ON cg.yclcg_code=cgs.yclcg_code
                            where cg.[dhzt]!=0
                            order by [dhcode]
                            OFFSET({ PageNumber} -1)*{ PageSize}ROWS
                            FETCH NEXT { PageSize} ROWS ONLY; ";
                List<Rm_yclcg> rm_Yclcgdh = connection.Query<Rm_yclcg>(sql).ToList();
                string sql1 = "SELECT COUNT(*) FROM [dbo].[Rm_yclcg] where [dhzt]!=0";
                int zon = connection.Query<int>(sql1).FirstOrDefault();
                FYRm_yclcg fYRm_Yclcgdh = new FYRm_yclcg()
                {
                    rm_Yclcgs = rm_Yclcgdh,
                    zon = zon,
                };
                return fYRm_Yclcgdh;

            }

        }


        public int Updhzt(string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[rm_yclcg] set [dhzt]=2 where [yclcg_code]='{code}'";
                int zt = connection.Execute(sql);
                return zt;
            }
        }
        public int Updhztbtg(string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[rm_yclcg] set   yclcg_djzt=3  where yclcg_code='{code}'";
                int zt = connection.Execute(sql);
                return zt;
            }
        }
        public int Uprk(Rm_yclcg rm_Yclcg)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = @$"update [dbo].[rm_yclcg] set rkzt='{rm_Yclcg.rkzt}',rklx='{rm_Yclcg.rklx}',rkcode='{rm_Yclcg.rkcode}',jhrktime='{rm_Yclcg.jhrktime}' where [yclcg_code]='{rm_Yclcg.yclcg_code}' ";
                int rk = connection.Execute(sql);
                return rk;
            }
        }
        public FYRm_yclcg Getrk(int PageNumber, int PageSize)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = @$"select 
		                        cg.[rkzt],
		                        cg.[rkcode],
                                cg.[rklx],
		                        cg.[dhcode],
		                        cg.[yclcg_code],
		                        cg.[yclcg_time],
		                        cg.[jhrktime],
		                        cg.[create_by],
		                        cg.[create_time],
		                        us.[user_name]
                        from [dbo].[rm_yclcg] cg
                        LEFT JOIN
                        [dbo].[user] as us
                         ON cg.create_by=us.user_id
                        where  cg.[rkzt]!=0
                               ORDER BY  [yclcg_id] 
                               OFFSET ({PageNumber}-1)*{PageSize} ROWS
                               FETCH NEXT {PageSize} ROWS ONLY;";
                List<Rm_yclcg> rm_Yclcg = connection.Query<Rm_yclcg>(sql).ToList();
                string sql1 = "SELECT COUNT(*) FROM [dbo].[Rm_yclcg] where [rkzt]!=0";
                int zon = connection.Query<int>(sql1).FirstOrDefault();
                FYRm_yclcg fYRm_Yclcg = new FYRm_yclcg()
                {
                    rm_Yclcgs = rm_Yclcg,
                    zon = zon,
                };
                return fYRm_Yclcg;
            }
        }

        public int Uprkzt(string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[rm_yclcg] set [rkzt]=2 where [rkcode]='{code}'";
                int rkzt= connection.Execute(sql);
                return rkzt;
            }
        }
        public int Updatesh(UpRm_yclcg upRm_Yclcg)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $@"update [dbo].[rm_yclcg] set [yclcg_zkje]='{upRm_Yclcg.yclcg_zkje}',[yclcg_zkl]='{upRm_Yclcg.yclcg_zkl}',[yclcg_fjje]='{upRm_Yclcg.yclcg_fjje}',
                                [yclcg_jsfs]='{upRm_Yclcg.yclcg_jsfs}',[djbz]='{upRm_Yclcg.djbz}',[yclcg_zje]='{upRm_Yclcg.yclcg_zje}',[yclcg_ssje]='{upRm_Yclcg.yclcg_ssje}',
                                 [yclcg_sl]='{upRm_Yclcg.yclcg_sl}',[yclcg_djzt]='{upRm_Yclcg.yclcg_djzt}'
                                 where [yclcg_code]='{upRm_Yclcg.yclcg_code}'";
                int sh = connection.Execute(sql);
                return sh;
            }
            
        }
    }
}
